package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import com.atsmart.app.BizApp;
import com.atsmart.app.R;
import com.atsmart.app.model.ReportHistoryInfo;
import com.atsmart.app.utils.StringUtil;
/**
 * TODO 数据库基类
 */
public class SqlHelper extends SQLiteOpenHelper {

	private static int DB_VERSION = 183;

	/**
	 * @return 返回SQL版本
	 */
	public static int getDBVersion() {
		return DB_VERSION;
	}

	public SqlHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(BizApp.getInstance().getString(R.string.report_history));
		db.execSQL(BizApp.getInstance().getString(R.string.position_attribute));//
		db.execSQL(BizApp.getInstance().getString(R.string.position_shutdown));
		db.execSQL(BizApp.getInstance().getString(R.string.task_info));//任务表
		db.execSQL(BizApp.getInstance().getString(R.string.number_info));
		db.execSQL(BizApp.getInstance().getString(R.string.position_history_info));
		//没有和任务相关联的汇报表
		db.execSQL(BizApp.getInstance().getString(R.string.template_info));
		
		db.execSQL(BizApp.getInstance().getString(R.string.message_info));
		db.execSQL(BizApp.getInstance().getString(R.string.templateDetail_info));
		
		db.execSQL(BizApp.getInstance().getString(R.string.productGroup_info));
		db.execSQL(BizApp.getInstance().getString(R.string.product_info));
		db.execSQL(BizApp.getInstance().getString(R.string.productItem_info));
		db.execSQL(BizApp.getInstance().getString(R.string.template_select_info));
		
		db.execSQL(BizApp.getInstance().getString(R.string.task_template_info));
		db.execSQL(BizApp.getInstance().getString(R.string.task_nums));
		//当前任务关联的汇报列表
		db.execSQL(BizApp.getInstance().getString(R.string.temp_info));
		db.execSQL(BizApp.getInstance().getString(R.string.user_info));
		db.execSQL(BizApp.getInstance().getString(R.string.user_imsi));
		db.execSQL(BizApp.getInstance().getString(R.string.templatelist));
		db.execSQL(BizApp.getInstance().getString(R.string.templateInfo));
		db.execSQL(BizApp.getInstance().getString(R.string.selectinfo));
		db.execSQL(BizApp.getInstance().getString(R.string.client_dis_info));
		db.execSQL(BizApp.getInstance().getString(R.string.client_info));
		db.execSQL(BizApp.getInstance().getString(R.string.context_info));
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion < newVersion) {
			String sql = "DROP TABLE IF EXISTS {0};";
			db.execSQL(StringUtil.format(sql, "selectinfo"));
			db.execSQL(StringUtil.format(sql, "templateInfo"));
			db.execSQL(StringUtil.format(sql, "templatelist"));
			db.execSQL(StringUtil.format(sql, "user_info"));
			db.execSQL(StringUtil.format(sql, "user_imsi"));
			db.execSQL(StringUtil.format(sql, "report_history"));
			db.execSQL(StringUtil.format(sql, "position_attribute"));
			db.execSQL(StringUtil.format(sql, "position_shutdown"));
			db.execSQL(StringUtil.format(sql, "task_info"));
			db.execSQL(StringUtil.format(sql, "number_info"));
			db.execSQL(StringUtil.format(sql, "position_history_info"));
			db.execSQL(StringUtil.format(sql, "template_info"));
			db.execSQL(StringUtil.format(sql, "client_info"));
			db.execSQL(StringUtil.format(sql, "message_infos"));
			db.execSQL(StringUtil.format(sql, "templateDetail_info"));
			db.execSQL(StringUtil.format(sql, "context_info"));
			db.execSQL(StringUtil.format(sql, "productGroup_info"));
			db.execSQL(StringUtil.format(sql, "product_info"));
			db.execSQL(StringUtil.format(sql, "productItem_info"));
			db.execSQL(StringUtil.format(sql, "template_select_info"));
			db.execSQL(StringUtil.format(sql, "client_dis_info"));
			db.execSQL(StringUtil.format(sql, "tem_info"));
			onCreate(db);
		} else {
			onCreate(db);
		}
	}


	public synchronized void addHistory(ReportHistoryInfo history) {
		if (history == null) {
			return;
		}
		SQLiteDatabase db = getWritableDatabase();
			String strSql = "insert or replace into report_history (name, desc, date, type) values(?, ?, ?, ?);";
			try {
				SQLiteStatement stm = db.compileStatement(strSql);
				stm.bindString(1, history.name == null ? "" : history.name);
				stm.bindString(2, history.desc == null ? "" : history.desc);
				stm.bindString(3, history.date == null ? "" : history.date);
				stm.bindLong(4, history.type);
				stm.execute();
				stm.clearBindings();
				stm.close();
			} catch (Exception e) {
				// will not happen
			}
	}

	public List<ReportHistoryInfo> getHistories(int type,int begin,int end) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = " select _id, name, desc, date, type from report_history   where type = " + type + "  order by date desc limit "+begin+","+end;
		Cursor cursor = db.rawQuery(sql, null);
		List<ReportHistoryInfo> histories = null;
		ReportHistoryInfo history = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				histories = new ArrayList<ReportHistoryInfo>();
				for (int i = 0; i < cursor.getCount(); i++) {
					history = new ReportHistoryInfo();
					history.id =  cursor.getInt(0);
					history.name = cursor.getString(1);
					history.desc = cursor.getString(2);
					history.date = cursor.getString(3);
					history.type = cursor.getInt(4);
					histories.add(history);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return histories;
	}
	public int getHistoriesCount(int type) {
		SQLiteDatabase db = getWritableDatabase();
		String sql = " select _id, name, desc, date, type  from report_history   where type = " + type + "  order by date desc";
		Cursor cursor = db.rawQuery(sql, null);
		List<ReportHistoryInfo> histories = null;
		ReportHistoryInfo history = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				histories = new ArrayList<ReportHistoryInfo>();
				for (int i = 0; i < cursor.getCount(); i++) {
					history = new ReportHistoryInfo();
					history.id =  cursor.getInt(0);
					history.name = cursor.getString(1);
					history.desc = cursor.getString(2);
					history.date = cursor.getString(3);
					history.type = cursor.getInt(4);
					histories.add(history);
					cursor.moveToNext();
				}
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		if(histories==null){
			return 0;
		}
		return histories.size();
	}
}
